#
# @author Wójcik Karol <wojcikk@student.agh.edu.pl>
# @licence http://www.gnu.org/copyleft/gpl.html GNU/GPL
#

class TeachersReportController < ApplicationController
layout 'standard'
before_filter :login_required

  def index
  params[:columns]  ||= ["year","subject","class_type","lgrzaj","lgrroz","lgodzin","sums"]
  @columns = params['columns']
  
	@users ||= Mark.find_by_sql("
	SELECT
	    marks.name as mark,
	    users.login as login,
      users.name as name,
	    users.surname as surname ,
	    lessons.semestr as semestr,
	    majors.name as major,
	    subjects.year as year,
	    subjects.name as subject,
	    class_types.name as class_type,
	    lessons_users.lgrroz,
	    lessons_users.lgrzaj,
	    lessons.lgodznazaj,
      subjects.id as sid,
	    lessons_users.lgrroz * lessons.lgodznazaj * class_types.ratio as lgodzin
	FROM
	    lessons_users
	LEFT JOIN users ON lessons_users.user_id  = users.id
	LEFT JOIN lessons ON lessons_users.lesson_id = lessons.id
	LEFT JOIN subjects ON lessons.subject_id = subjects.id
	LEFT JOIN class_types ON lessons.class_type_id = class_types.id
	LEFT JOIN marks ON users.mark_id = marks.id
	LEFT JOIN majors ON subjects.major_id = majors.id
	ORDER BY surname,name,semestr DESC,major,year,subject
	")
	
	@sums ||= Mark.find_by_sql("
  SELECT 
      study_types.name as study_type, 
      marks.name as mark,
      users.login as login,
      users.name as name,
      users.surname as surname,
      SUM(lessons_users.lgrroz * lessons.lgodznazaj * class_types.ratio) as all
  FROM 
      users
  LEFT JOIN lessons_users ON lessons_users.user_id = users.id 
  LEFT JOIN lessons ON lessons_users.lesson_id = lessons.id 
  LEFT JOIN class_types ON lessons.class_type_id = class_types.id
  LEFT JOIN marks ON users.mark_id = marks.id
  LEFT JOIN subjects ON lessons.subject_id = subjects.id
  LEFT JOIN majors ON subjects.major_id = majors.id
  LEFT JOIN study_types ON majors.study_type_id = study_types.id 
  GROUP BY marks.name,users.login,users.name,users.surname,study_types.name
  ") 


end

def reports_user
  params[:columns]  ||= ["year","subject","class_type","lgrzaj","lgrroz","lgodzin","sums"]
  @columns = params['columns']
  
  @my_id = (@session['user'].id).to_s
  @my_login = (@session['user'].login).to_s
  @ready = []
  
  @users ||= Mark.find_by_sql("
  SELECT
      marks.name as mark,
      users.login as login,
      users.name as name,
      users.surname as surname ,
      lessons.semestr as semestr,
      majors.name as major,
      subjects.year as year,
      subjects.name as subject,
      class_types.name as class_type,
      lessons_users.lgrroz,
      lessons_users.lgrzaj,
      lessons.lgodznazaj,
      lessons_users.lgrroz * lessons.lgodznazaj * class_types.ratio as lgodzin,
     subjects.id as sid
  FROM
      lessons_users
  LEFT JOIN users ON lessons_users.user_id  = users.id
  LEFT JOIN lessons ON lessons_users.lesson_id = lessons.id
  LEFT JOIN subjects ON lessons.subject_id = subjects.id
  LEFT JOIN class_types ON lessons.class_type_id = class_types.id
  LEFT JOIN marks ON users.mark_id = marks.id
  LEFT JOIN majors ON subjects.major_id = majors.id
  ORDER BY surname,name,semestr DESC,major,year,subject
  ")
  
  @users.each do |us| 
    if us.login == @my_login
      @ready << us
    end
  end
  
  @users = @ready
  
  @sums ||= Mark.find_by_sql("
  SELECT 
      study_types.name as study_type, 
      marks.name as mark,
      users.login as login,
      users.name as name,
      users.surname as surname,
      SUM(lessons_users.lgrroz * lessons.lgodznazaj * class_types.ratio) as all
  FROM 
      users
  LEFT JOIN lessons_users ON lessons_users.user_id = users.id 
  LEFT JOIN lessons ON lessons_users.lesson_id = lessons.id 
  LEFT JOIN class_types ON lessons.class_type_id = class_types.id
  LEFT JOIN marks ON users.mark_id = marks.id
  LEFT JOIN subjects ON lessons.subject_id = subjects.id
  LEFT JOIN majors ON subjects.major_id = majors.id
  LEFT JOIN study_types ON majors.study_type_id = study_types.id 
  GROUP BY marks.name,users.login,users.name,users.surname,study_types.name
  ") 
  
  @ready = []
  @sums.each do |su| 
    if su.login == @my_login
      @ready << su
    end
  end
  
  @ready = @sums
  
end

#metoda odpowiedzialna za gromadzenie informacji o przedmiocie o id podanychw  wywolaniu (tzn wszyscy prowadzacy ten przedmiot)
def detail_subject
  
  @subject = Subject.find(params[:id])
  @lessons = Lesson.find(:all, :conditions => ["subject_id = ?", params[:id]])

end


end
